﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for QuoteBO
    /// </summary>
    public class QuoteBO : DBConnectionBO
    {
        public QuoteBO()
            : base()
        {

        }

        /// <summary>
        /// Get list quote
        /// </summary>
        /// <returns></returns>
        public List<Quote> GetListQuote()
        {
            var listQuote = new List<Quote>();
            var query = "SELECT * FROM [PrivateBlog].[dbo].[Quotes]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            this.CloseConnection();
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var quote = new Quote
                    {
                        Id = Convert.ToInt32(dataTable.Rows[i][0]),
                        IdUser = Convert.ToInt32(dataTable.Rows[i][1]),
                        Content = (string) dataTable.Rows[i][2],
                        Author = (string) dataTable.Rows[i][3],
                        LastDateUpdated = (DateTime) dataTable.Rows[i][4],
                        LastUpdatedBy = (string) dataTable.Rows[i][5],
                        Status = Convert.ToInt32(dataTable.Rows[i][0])
                    };

                listQuote.Add(quote);
            }
            return listQuote;
        }

        /// <summary>
        /// Add a new quote
        /// </summary>
        /// <param name="quote"></param>
        /// <returns></returns>
        public int AddQuote(Quote quote)
        {
            var query = "INSERT INTO [PrivateBlog].[dbo].[Quotes]([IdUser],[Content],[Author],[LastDateUpdated],[LastUpdatedBy],[Status]) VALUES (" +
                        "" + quote.IdUser +
                        ", N'" + quote.Content +
                        "', N'" + quote.Author+
                        "', GETDATE(), '" + quote.LastUpdatedBy +
                        "', " + quote.Status + ")";            
            var sqlCommand = new SqlCommand(query, connection);            
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a quote
        /// </summary>
        /// <param name="quote"></param>
        public int EditQuote(Quote quote)
        {
            var query = "UPDATE [PrivateBlog].[dbo].[UserRoles]" +
                        "SET IdUser = N'" + quote.IdUser + "', " +
                        "Content = N'" + quote.Content + "', " +
                        "Author = N'" + quote.Author + "', " +
                        "LastDateUpdated = GETDATE(), " +
                        "LastUpdatedBy = N'" + quote.LastUpdatedBy + ", " +
                        "Status = " + quote.Status +
                        "WHERE Id = " + quote.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a quote
        /// </summary>
        /// <param name="quote"></param>
        public int DeleteQuote(Quote quote)
        {
            var query = "DELETE FROM [PrivateBlog].[dbo].[Quotes] " +
                        " WHERE Id = " + quote.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Get random quote
        /// </summary>
        /// <returns></returns>
        public List<Quote> GetRandomQuote()
        {
            var listQuote = new List<Quote>();
            var rand = new Random();
            var number = rand.Next(1, GetListQuote().Count + 1);

            var query = "SELECT * FROM [PrivateBlog].[dbo].[Quotes] WHERE Id = " + number + " and Status = 1";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            this.CloseConnection();
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var quote = new Quote
                {
                    Id = Convert.ToInt32(dataTable.Rows[i][0]),
                    IdUser = Convert.ToInt32(dataTable.Rows[i][1]),
                    Content = (string)dataTable.Rows[i][2],
                    Author = (string)dataTable.Rows[i][3],
                    LastDateUpdated = (DateTime)dataTable.Rows[i][4],
                    LastUpdatedBy = (string)dataTable.Rows[i][5],
                    Status = Convert.ToInt32(dataTable.Rows[i][0])
                };

                listQuote.Add(quote);
            }

            return listQuote;
        }

    }
}